package com.shopping.dao.impl;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.shopping.dao.UserDao;
import com.shopping.pojo.Order;
import com.shopping.pojo.User;
import com.shopping.utils.C3p0Utils;

public class UserDaoImpl implements UserDao {

	@Override
	public boolean insert(User user) {
		// 创建QueryRunner对象
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		// 写SQL语句
		String sql = "insert into users(username,password,email,sex,mobile,create_time,status) value(?,?,?,?,?,?,?)";
		// 调用方法
		int num;
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sd.format(user.getCreate_time());
		try {
			num = runner.update(sql, new Object[] {user.getUsername(), 
					user.getPassword(), user.getEmail(), user.getSex(),
					user.getMobile(), date, user.getStatus()});
			if(num > 0)
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public User find(int id) {
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users where id=?";
		try {
			User user = (User) runner.query(sql, new BeanHandler(User.class), new Object[] {id});
			return user;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public boolean update(User user) {
		// TODO 自动生成的方法存根
		return false;
	}

	@Override
	public boolean delete(int id) {
		QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "delete from users where id=?";
		try {
			int n = runner.update(sql, id);
			if(n > 0)
				return true;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}	

	@Override
	public User findUserByUsernameAndPassword(String username, String password) {
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users where username=? and password=?";
		try {
			User user = qr.query(sql, new BeanHandler<User>(User.class), username, password);
			return user;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public ArrayList<User> findAll() {
		ArrayList<User> list = new ArrayList<>();
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users";
		try {
			list = (ArrayList<User>) qr.query(sql, new BeanListHandler<>(User.class));
			return list;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) throws SQLException {
		UserDao ud = new UserDaoImpl();
		ArrayList<User> list;
		list = ud.findPage(2, 2);
		System.out.println("list" + list);
		for(Object obj : list) {
			System.out.println(obj);
		}
	}

	@Override
	public ArrayList<User> findPage(int page, int limit) {
		ArrayList<User> list = new ArrayList<User>();
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from users limit " + (page-1)*limit + "," + limit;
		try {
			list = (ArrayList<User>) qr.query(sql, new BeanListHandler<>(User.class));
			return list;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public long TotalData() {
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select count(*) from users";
		long n = 0;
		try {
			n = (long) qr.query(sql, new ScalarHandler());
		}catch (Exception e) {
			e.printStackTrace();
		}
		return n;
	}
	

}

